﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace DoeObjects
{
    public class Loanable
    {

        public int LoanableId { get; set; }
        public string Barcode { get; set; }
        public LoanableCategory Category { get; set; }
        public User Owner { get; set; }
        public string Location { get; set; }
        public Title Title { get; set; }

        public bool UnavailableFromOwner { get; set; }
		public bool Deleted { get; set; }

        public Status Status
        {
            get
            {
                return getStatus(this.LoanableId);
            }
        }

        private const string BASE_SQL = @"
SELECT * FROM Loanable 
INNER JOIN Title 
ON Loanable.TitleId = Title.TitleId 
INNER JOIN Publisher 
ON Title.PublisherId = Publisher.PublisherId 
INNER JOIN LoanableCategory 
ON Loanable.LoanableCategoryId = LoanableCategory.LoanableCategoryId 
INNER JOIN [User] 
ON Loanable.LoanableOwner = [User].UserId
INNER JOIN [UserCategory]
ON [User].CategoryId = [UserCategory].UserCategoryId
";


        public static bool uniqueDoeLibSIdentifier(string identifier)
        {
            string SQL = "SELECT Barcode FROM Loanable WHERE Loanable.Barcode = @DoeID";
            List<Loanable> results = new List<Loanable>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            cmd.Parameters.AddWithValue("@DoeID", identifier);

            string result = "";
            try
            {
                con.Open();
                result = cmd.ExecuteScalar() as string;
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }

            if (result == null)
                return true;
            else
                return false;
        }

        
        public static List<Loanable> getLoanablesByOwner(int ownerID)
        {
			//Changed for deleted
            string SQL = BASE_SQL + " WHERE Loanable.LoanableOwner = @ownerID AND Loanable.Deleted != 1";
            List<Loanable> results = new List<Loanable>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            cmd.Parameters.AddWithValue("@ownerID", ownerID);
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    results.Add(getCurrentLoanable(dar, con));
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;

        }

        public static List<Loanable> getBorrowedLoanablesByOwner(int ownerId)
        {
            //get all loanables from the user:
            List<Loanable> userLoanables = getLoanablesByOwner(ownerId);
            List<Loanable> currentlyBorrowedLoanablesFromOwner = new List<Loanable>();

            foreach (Loanable loanable in userLoanables)
            {
                List<Loan> loansFromLoanable = Loan.getLoansFromLoanable(loanable.LoanableId);

                if (loansFromLoanable.Where(l => l.ReturnDate == DateTime.MinValue).ToList().Count > 0)
                {
                    currentlyBorrowedLoanablesFromOwner.Add(loanable);
                }
            }

            return currentlyBorrowedLoanablesFromOwner;
        }

        public static List<Loanable> getLoanablesByOwnerAndSearch(int ownerID, string search)
        {
			//Changed for deleted
            string SQL = BASE_SQL + " WHERE Loanable.LoanableOwner = @ownerID AND Title.Title LIKE %@search% AND Loanable.Deleted != 1";
            List<Loanable> results = new List<Loanable>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            cmd.Parameters.AddWithValue("@ownerID", ownerID);
            cmd.Parameters.AddWithValue("@search", search);
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    results.Add(getCurrentLoanable(dar, con));
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;

        }

        public static List<Loanable> getAllLoanables()
        {

            string SQL = BASE_SQL;
            List<Loanable> results = new List<Loanable>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    results.Add(getCurrentLoanable(dar, con));
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;

        }

        internal static Loanable getCurrentLoanable(SqlDataReader dar, SqlConnection connection)
        {
            Loanable loanable = null;

            if (dar != null)
            {
                loanable = new Loanable();
                loanable.LoanableId = Convert.ToInt32(dar["LoanableId"]);
                loanable.Barcode = dar["Barcode"] as string;
                loanable.Location = dar["Location"] as string;
                loanable.Title = Title.getCurrentTitle(dar);
                loanable.Owner = User.getCurrentUser(dar);
                loanable.Category = LoanableCategory.getCurrentLoanableCategory(dar, connection);
                loanable.UnavailableFromOwner = (bool)dar["UnavailableFromOwner"];
				//Changed for deleted
				loanable.Deleted = (bool)dar["Deleted"];
            }

            return loanable;
        }

        public static List<Loanable> getLoanablesBySearch(string search)
        {
			//Changed for deleted
            string SQL = BASE_SQL + " WHERE Title.Title LIKE %@search% AND Loanable.Deleted != 1";
            List<Loanable> results = new List<Loanable>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            cmd.Parameters.AddWithValue("@search", search);
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    results.Add(getCurrentLoanable(dar, con));
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;
        }


        /// <summary>
        /// dont use this function. It will throw an Exception. Use the checkIn() method of an Loan object instead.
        /// </summary>
        /// <returns></returns>
        public bool checkIn()
        {
            throw new NotImplementedException();
            //this functionality was implemented based on a Loan in the Loan class, because you can only check in an existing loan
            //this method is only here nobody else wonders where this method is and implements it instead...
        }

        /// <summary>
        /// creates a new loan for this loanable
        /// </summary>
        /// <param name="borrowerId"></param>
        /// <returns></returns>
        public Loan checkOut(User borrower)
        {
            //check if it is available
            if (!Loan.userHasActiveLoanForTitle(this.Title.TitleId, borrower.UserId) //check that user has currently no active loan for this title
                && ((getStatus(this.LoanableId) == Status.AVAILABLE && Title.isAvalible(this.Title.TitleId))
					|| getStatus(this.LoanableId) == Status.RESERVED && Reservation.userHasAvalibleReservation(borrower.UserId, this.Title.TitleId)) //if the title is reserved, check if the user has a available reservation for it
				&& !borrower.IsPendingExpiration) //User must not be about to expire
            //if(getStatus(this.LoanableId) == Status.AVAILABLE && !Loan.userHasActiveLoanForTitle(this.Title.TitleId, borrower.UserId) && (Title.isAvalible(this.Title.TitleId) || Reservation.userHasAvalibleReservation(borrower.UserId, this.Title.TitleId)))
            {
                Loan newLoan = new Loan();
                newLoan.BorrowDate = DateTime.Now;
                newLoan.Borrower = borrower;
                newLoan.Loanable = this;
                newLoan.ReturnDate = DateTime.MinValue;

                if (newLoan.save())
                {
                    if (Reservation.userHasAvalibleReservation(borrower.UserId, this.Title.TitleId))
                    {
                        Reservation userReservationForTitle = Reservation.getReservationByTitleAndUser(this.Title.TitleId, borrower.UserId);
                        Reservation.delete(userReservationForTitle.ReservationId);
                    }

                    return newLoan;
                }
            }

            return null;
        }

        public static Loanable getById(int id)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			//Changed for delete
            SqlCommand command = new SqlCommand(BASE_SQL + " WHERE loanableId = @id AND Loanable.Deleted != 1", connection);
            command.Parameters.AddWithValue("@id", id);

            Loanable loanable = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    loanable = getCurrentLoanable(reader, connection);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return loanable;
        }

        public static Loanable getLoanablesByDoelibsIdentifier(string identifier)
        {
			//Changed for delete
            string SQL = BASE_SQL + " WHERE Loanable.Barcode = @identifier AND Loanable.Deleted != 1" ;
            
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            cmd.Parameters.AddWithValue("@identifier", identifier);

            Loanable result = null;
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                if (dar.Read())
                {
                    result = getCurrentLoanable(dar, con);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }

            return result;

        }

        public static List<Loanable> getLoanablesByTitle(int titleId)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand command = new SqlCommand(BASE_SQL + " WHERE title.titleId = @id AND Loanable.Deleted != 1", connection);
            command.Parameters.AddWithValue("@id", titleId);

            List<Loanable> loanables = new List<Loanable>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    loanables.Add(getCurrentLoanable(reader, connection));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return loanables;
        }

        /// <summary>
        /// returns the status of a loanable
        /// </summary>
        /// <param name="loanableId"></param>
        /// <returns></returns>
        public static Status getStatus(int loanableId)
        {
            if (loanableId == 0)
            {
                return Status.UNKNOWN; //loanable is not persistend
            }

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			//Changed for delete
            SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM loan WHERE loanableId = @id AND returnDate IS NULL", connection);
            command.Parameters.AddWithValue("@id", loanableId);

            Status status = Status.UNKNOWN;
            try
            {
                connection.Open();
                int count = (int)command.ExecuteScalar();

                Loanable loanable = Loanable.getById(loanableId);
                if (count == 0)
                {
                    //status = Status.AVAILABLE

					//If loanable has been deleted
					if (loanable.Deleted)
						return Status.DELETED;

                    //it is available, but could be marked as unavailableFromOwner
                    if (loanable.UnavailableFromOwner)
                    {
                        status = Status.UNAVAILABLE_FROM_OWNER;
                    }
                    else
                    {
                        //loanable is available, check if it is reserved
                        if (Title.isReserved(loanable.Title.TitleId, true))
                        {
                            //check if there are more reservations than loanables of this title

                            //if (Loanable.getLoanablesByTitle(loanable.Title.TitleId).Count < Reservation.getReservations(loanable.Title.TitleId).Count)
                            if (Title.isAvalible(loanable.Title.TitleId))
                            {
                                status = Status.AVAILABLE;
                            }
                            else
                            {
                                status = Status.RESERVED;
                            }
                        }
                        else
                        {
                            //there is currently no reservation where the loanable is recalled
                            status = Status.AVAILABLE;
                        }
                    }
                }
                else
                {
                    //it is currently borrowed

                    //check if owner recalled it
                    if (loanable.UnavailableFromOwner)
                    {
                        status = Status.RECALLED;
                    }
                    else
                    {
                        status = Status.BORROWED;
                    }
                }
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return status;
        }

        public static int add(Loanable loanable)
        {
            int lastInsertedId = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			//Changed for delete
            SqlCommand cmd = new SqlCommand("INSERT INTO loanable (Barcode, Location, LoanableOwner, LoanableCategoryId, TitleId, UnavailableFromOwner, Deleted) OUTPUT INSERTED.loanableId VALUES (@Barcode, @Location, @Owner, @CategoryId, @TitleId, 0,0)", connection);


            SqlParameter barcodeParam = new SqlParameter("@Barcode", SqlDbType.NVarChar);
            barcodeParam.Value = loanable.Barcode;
            SqlParameter locationParam = new SqlParameter("@Location", SqlDbType.NVarChar);
            locationParam.Value = loanable.Location;

            cmd.Parameters.Add(barcodeParam);
            cmd.Parameters.Add(locationParam);
            cmd.Parameters.AddWithValue("@Owner", loanable.Owner.UserId);
            cmd.Parameters.AddWithValue("@CategoryId", loanable.Category.CategoryId);
            cmd.Parameters.AddWithValue("@TitleId", loanable.Title.TitleId);

            try
            {
                connection.Open();
                Object res = cmd.ExecuteScalar();
                if (res != null)
                {
                    lastInsertedId = (int)res;

                    if (Title.isReserved(loanable.Title.TitleId, false))
                    {
                        Reservation reservation = Reservation.getNext(loanable.Title.TitleId, false);
                        reservation.LoanRecalled = true;
                        reservation.AvailableDate = DateTime.Now;
                        reservation.save();

                        //Notify user that loanable is available for him...
                        Notification notification = new Notification();
                        notification.Recipient = reservation.User;
                        notification.SendDate = DateTime.Now;
                        notification.Sender = null;
                        notification.Type = NotificationType.TITLE_RESERVATION;
                        notification.Message = string.Format("You reserved {0}. This book is now available for you. Please pick it up till {1}", reservation.Title.BookTitle, reservation.AvailableDate.AddDays(Reservation.RESERVATION_EXPIRES_TIMER_IN_DAYS).ToShortDateString());
                        notification.save();
                    }

                }
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return lastInsertedId;
        }

        public static bool update(Loanable loanable)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			//Changed for delete
            SqlCommand cmd = new SqlCommand("UPDATE loanable SET Barcode = @Barcode, Location = @Location, LoanableOwner = @Owner, LoanableCategoryId = @CategoryId, TitleId = @TitleId, UnavailableFromOwner = @UnavailableFromOwner, Deleted = @Deleted WHERE loanableId = @id", connection);


            SqlParameter barcodeParam = new SqlParameter("@Barcode", SqlDbType.NVarChar);
            barcodeParam.Value = loanable.Barcode;
            SqlParameter locationParam = new SqlParameter("@Location", SqlDbType.NVarChar);
            locationParam.Value = loanable.Location;

            cmd.Parameters.Add(barcodeParam);
            cmd.Parameters.Add(locationParam);
            cmd.Parameters.AddWithValue("@id", loanable.LoanableId);
            cmd.Parameters.AddWithValue("@Owner", loanable.Owner.UserId);
            cmd.Parameters.AddWithValue("@CategoryId", loanable.Category.CategoryId);
            cmd.Parameters.AddWithValue("@TitleId", loanable.Title.TitleId);
            cmd.Parameters.AddWithValue("@UnavailableFromOwner", (loanable.UnavailableFromOwner) ? 1 : 0);
			cmd.Parameters.AddWithValue("@Deleted", (loanable.Deleted) ? 1 : 0);
			
            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

        /// <summary>
        /// saves a loanable in the database. depending on the LoanableId it will be inserted or updated. if a new one is created (insert) the Id in the object will be updated
        /// </summary>
        /// <returns></returns>
        public bool save()
        {
            if (this.LoanableId == 0)
            {
                this.LoanableId = Loanable.add(this);
                return (this.LoanableId != 0);
            }
            else
            {
                return Loanable.update(this);
            }
        }

		public static bool delete(Loanable loanable) 
		{
			//Check if possible to delete
			if (loanable.Status == Status.AVAILABLE || loanable.Status == Status.UNAVAILABLE_FROM_OWNER)
			{
				//Check if its the last copy
					if (Loanable.getLoanablesByTitle(loanable.Title.TitleId) == null)
					{
						//get reservations
						List<Reservation> reservations = Reservation.getReservationsByTitleId(loanable.Title.TitleId); 

						//foreach send notification to user
						if (reservations != null)
						{
							foreach (Reservation item in reservations)
							{
								Notification notification = new Notification();
								notification.Recipient = item.User;
								notification.Message = string.Format("All copis of {0} has either been deleted or lost. we are sorry for the inconvenience", loanable.Title);
								notification.Type = NotificationType.TITLE_RESERVATION;
								notification.save();
								//Delete Reservation
								Reservation.delete(item.ReservationId);
							}
						}
					}
					//Remove loanable

					loanable.Deleted = true;
					loanable.save();
					return true;
			//		int affectedRows = 0;
			//		SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			//		SqlCommand cmd = new SqlCommand("DELETE FROM Loanable WHERE loanableId = @id", connection);

			//		cmd.Parameters.AddWithValue("@id", loanable.LoanableId);

			//		try
			//		{
			//			connection.Open();
			//			affectedRows = cmd.ExecuteNonQuery();
			//		}
			//		catch (SqlException er)
			//		{
			//			//oo
			//			throw er;
			//		}
			//		finally
			//		{
			//			if (connection != null && connection.State != ConnectionState.Closed)
			//			{
			//				connection.Close();
			//			}
			//		}
			//		return (affectedRows > 0);
				}
			return false;
		}

        public static List<Loanable> getByCategory(int locationCategoryId)
        {
            List<Loanable> results = new List<Loanable>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
			//Changed for delete
            SqlCommand cmd = new SqlCommand(BASE_SQL + " WHERE Loanable.LoanableCategoryId = @id AND Loanable.Deleted != 1", con);
            cmd.Parameters.AddWithValue("@id", locationCategoryId);

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    results.Add(getCurrentLoanable(dar, con));
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;
        }

        /// <summary>
        /// recalls the loanable cause of a owner recall
        /// </summary>
        public static void OwnerRecall(int loanableId)
        {
            Loanable loanable = Loanable.getById(loanableId);
            
            //check if there are available reservations. if so, send message that it cannot be picked up...
            if (loanable.Status == Status.RESERVED)
            {
                Reservation reservation = Reservation.getNewestReservationWithAvailableLoanable(loanable.Title.TitleId);

                if (reservation != null)
                {
                    reservation.LoanRecalled = false;
                    reservation.AvailableDate = DateTime.MinValue;
                    reservation.save();

                    //send notification
                    Notification notification = new Notification();
                    notification.Recipient = reservation.User;
                    notification.SendDate = DateTime.Now;
                    //notification.Sender = loanable.Owner;
                    notification.Type = NotificationType.TITLE_RESERVATION;
                    notification.Message = string.Format("The loanable for {0} which was reserved for you was recalled by the owner. We will inform you when another loanable will be available again.", loanable.Title.BookTitle);
                    notification.save();
                }
            }
            else if (loanable.Status == Status.BORROWED)
            {
                //recall loanable
                Loan currentLoanForThisLoanable = Loan.getCurrentActiveLoanForLoanable(loanableId);
                if (currentLoanForThisLoanable != null)
                {
                    currentLoanForThisLoanable.recall(RecallReason.OWNER_RECALL);
                }
            }

            //change status of loanable
            loanable.UnavailableFromOwner = true;
            loanable.save();
        }

		/// <summary>
		/// mark the loanable as available again after owner setting it to unavailable
		/// </summary>
		public bool markAsAvailable()
		{
			if (Owner.IsPendingExpiration) return false;
			UnavailableFromOwner = false;
			if (save())
			{
				//check if there are unhandled reservations for this title
				Reservation nextReservation = Reservation.getNext(Title.TitleId, false);
				if (nextReservation != null)
				{
					nextReservation.LoanRecalled = true;
					nextReservation.AvailableDate = DateTime.Now;
					nextReservation.save();

					//send notification
					Notification notification = new Notification();
					notification.Recipient = nextReservation.User;
					notification.SendDate = DateTime.Now;
					notification.Type = NotificationType.TITLE_RESERVATION;
					notification.Message = string.Format("You reserved {0}. This book is now available for you. Please pick it up till {1}", nextReservation.Title.BookTitle, nextReservation.AvailableDate.AddDays(Reservation.RESERVATION_EXPIRES_TIMER_IN_DAYS).ToShortDateString());
					notification.save();
				}
				return true;
			}
			return false;
		}

        /// <summary>
        /// returns a barcode which is not in use
        /// </summary>
        /// <returns></returns>
        public static string getFreeBarcode() {
            string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
            char[] barcode = new char[10];
            Random random = new Random();

            string identifier;
            
            do
            {
                for (int i = 0; i < barcode.Length; i++)
                {
                    barcode[i] = chars[random.Next(chars.Length)];
                }
                identifier = new string(barcode);
            } while (getLoanablesByDoelibsIdentifier(identifier) != null); 
            
            return identifier;
        }

		public static bool transferLoanablesToUser(List<int> transferLoanablesIds, int fromUserId, int toUserId)
		{
			if (transferLoanablesIds.Count == 0) return false;
			int affectedRows = 0;

			SqlConnection connection = new SqlConnection(Settings.ConnectionString);

			StringBuilder builder = new StringBuilder();
			builder.Append("UPDATE Loanable SET LoanableOwner=@NewOwner WHERE LoanableOwner=@FromOwner");
			for (int i = 0; i < transferLoanablesIds.Count; i++)
			{
				if (i == 0 && transferLoanablesIds.Count == 1)
					builder.Append(" AND LoanableId=" + transferLoanablesIds[i].ToString());
				else if (i == 0)
					builder.Append(" AND (LoanableId=" + transferLoanablesIds[i].ToString());
				else if (i == transferLoanablesIds.Count - 1)
					builder.Append(" OR LoanableId=" + transferLoanablesIds[i].ToString() + ")");
				else
					builder.Append(" OR LoanableId=" + transferLoanablesIds[i].ToString());
			}

			SqlCommand cmd = new SqlCommand(builder.ToString(), connection);

			SqlParameter fromOwnerParam = new SqlParameter("@FromOwner", SqlDbType.Int) { Value = fromUserId };
			SqlParameter toOwnerParam = new SqlParameter("@NewOwner", SqlDbType.Int) { Value = toUserId };
			cmd.Parameters.Add(fromOwnerParam);
			cmd.Parameters.Add(toOwnerParam);

			try
			{
				connection.Open();
				affectedRows = cmd.ExecuteNonQuery();
			}
			catch (SqlException er)
			{
				throw er;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}

			return (affectedRows > 0);
		}
	}
}
